﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace xmlBookstore.classLibrary
{
    public class DataRep
    {
        private Inventory inventory = new Inventory();
        private CategoryList categoryList = new CategoryList();
        private SqlConnection sqlConn = new SqlConnection("Data Source=fallenstd.webhop.org;Initial Catalog=nmbpXML;Persist Security Info=True;User ID=nmbp;Password=nmbp");
        private SqlDataReader rdr;
        private SqlCommand cmd;
        private XDocument xmlDoc;
        private XmlNodeReader Nr;

        private List<string> books = new List<string>();    // cemu ovo sluzi? ne koristi se nigdje

        public DataRep()
        {
            try
            {
                sqlConn.Open();

                //get books
                cmd = new SqlCommand("SELECT docXML.query('/') AS BOOKS from XMLdocs WHERE docName='books'", sqlConn);
                rdr = cmd.ExecuteReader();
                xmlDoc = new XDocument();
                while (rdr.Read())
                {
                    xmlDoc = XDocument.Parse(rdr["BOOKS"].ToString());
                }
                rdr.Close();

                var query = from book in xmlDoc.Elements("books").Elements("book")
                            select book;
                foreach (var b in query)
                {
                    if (b != null)
                    {
                        var book = new Book(Convert.ToInt32(b.Element("id").Value),
                                            b.Element("title").Value, b.Element("author").Value,
                                            Convert.ToInt32(b.Element("year").Value),
                                            new Category(b.Element("category").Value, b.Element("type").Value));
                        string sql =
                            string.Format("SELECT docXML.query('for $i in //item " +
                                            "where $i/id={0} return ($i) ') AS ITEM " +
                                           "FROM XMLdocs WHERE (docName = 'inventory') ",
                                book.Id);

                        cmd = new SqlCommand(sql, sqlConn);
                        rdr = cmd.ExecuteReader();

                        while (rdr.Read())
                        {

                            try
                            {
                                xmlDoc = XDocument.Parse(rdr["ITEM"].ToString());
                            }
                            catch (Exception e)
                            {

                            }
                        }
                        rdr.Close();
                        if (xmlDoc != null)
                        {
                            book.Price = Convert.ToDouble(xmlDoc.Element("item").Element("price").Value.ToString());
                            book.CountInStorage = Convert.ToInt32(xmlDoc.Element("item").Element("warehouseAmount").Value.ToString());
                        }
                        inventory.AddBook(book);
                    }
                }


            }
            catch (Exception e)
            {
                throw;

            }

            // ovaj dio prolazi kroz sve knjige ucitane iz baze i provjerava jel se njihove kategorije vec nalaze
            // u categoryList objektu; ako se ne nalaze, dodaje ih unutra
            makeCategoryList();
            
        }

        public void makeCategoryList()
        {
            try
            {
                bool alreadyInCategoryList;
                categoryList.Categories.Clear();
                categoryList.Categories.Add(new Category("", "")); // ovo sluzi kao "prazna" kategorija da se moze iskljuciti filtriranje po kategorijama
                foreach (Book book in inventory.Books)
                {
                    alreadyInCategoryList = false;
                    foreach (Category category in categoryList.Categories)
                    {
                        if (book.Cat.CatName == category.CatName && book.Cat.TypeName == category.TypeName) alreadyInCategoryList = true;

                    }
                    if (alreadyInCategoryList == false) categoryList.Categories.Add(book.Cat);
                }
            }
            catch (Exception e)
            {
                throw;
            }

        }

        public Inventory getInventory
        {
            get { return inventory; }
        }
        public CategoryList getCategoryList
        {
            get { return categoryList; }
        }


    }
}
